#!/usr/bin/env python3
"""
数据库调试脚本
用于直接检查SQLite数据库表结构和数据
"""
import sqlite3
import os

# 获取当前目录
base_dir = os.path.dirname(os.path.abspath(__file__))

# 数据库文件路径
db_path = os.path.join(base_dir, "flowscape.db")

print("=" * 60)
print("数据库调试工具")
print("=" * 60)
print(f"数据库文件: {db_path}")
print(f"文件存在: {os.path.exists(db_path)}")

if not os.path.exists(db_path):
    print("错误: 数据库文件不存在!")
    exit(1)

# 连接到数据库
print("\n连接到数据库...")
conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 1. 检查表是否存在
print("\n1. 检查表是否存在:")
cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='file_uploads';")
table_exists = cursor.fetchone()
print(f"file_uploads表存在: {bool(table_exists)}")

# 2. 检查表结构
print("\n2. 文件上传表结构:")
print("-" * 60)
print(f"{'列名':<30} | {'类型':<20} | {'默认值':<10} | {'注释'}")
print("-" * 60)
cursor.execute("PRAGMA table_info(file_uploads);")
columns = cursor.fetchall()

# 检查是否有departure_retention_status列
has_departure_column = False
for col in columns:
    col_id, col_name, col_type, not_null, default_val, pk = col
    print(f"{col_name:<30} | {str(col_type):<20} | {str(default_val):<10} | {''}")
    if col_name == 'departure_retention_status':
        has_departure_column = True

print("-" * 60)
print(f"departure_retention_status列存在: {has_departure_column}")
print(f"总列数: {len(columns)}")

# 3. 显示前几条记录
print("\n3. 示例数据 (最多5条):")
try:
    # 先尝试不包含新字段的简单查询
    cursor.execute("SELECT id, filename, original_filename, upload_time FROM file_uploads LIMIT 5;")
    records = cursor.fetchall()
    print(f"找到 {len(records)} 条记录")
    for record in records:
        print(f"ID: {record[0]}, 文件名: {record[1]}, 上传时间: {record[3]}")
    
    # 如果departure_retention_status列存在，尝试查询它
    if has_departure_column:
        print("\n尝试查询departure_retention_status字段:")
        cursor.execute("SELECT id, departure_retention_status FROM file_uploads LIMIT 3;")
        records = cursor.fetchall()
        for record in records:
            print(f"ID: {record[0]}, 状态: {record[1]}")
    else:
        print("\n跳过departure_retention_status查询，因为列不存在")
        
        # 建议运行迁移脚本
        print("\n建议运行数据库迁移脚本添加缺失的列:")
        print("  python migrate_database.py")
        
        # 直接在调试脚本中尝试添加列
        print("\n尝试直接添加缺失的列...")
        try:
            cursor.execute("ALTER TABLE file_uploads ADD COLUMN departure_retention_status TEXT DEFAULT '未分析';")
            cursor.execute("ALTER TABLE file_uploads ADD COLUMN background_activity_status TEXT DEFAULT '未分析';")
            cursor.execute("ALTER TABLE file_uploads ADD COLUMN anomaly_detection_status TEXT DEFAULT '未分析';")
            conn.commit()
            print("✓ 成功添加缺失的列!")
            print("\n重新检查表结构...")
            cursor.execute("PRAGMA table_info(file_uploads);")
            updated_columns = cursor.fetchall()
            print(f"更新后的总列数: {len(updated_columns)}")
        except Exception as e:
            print(f"✗ 添加列失败: {e}")
            conn.rollback()
except Exception as e:
    print(f"查询数据时出错: {e}")

# 关闭连接
conn.close()
print("\n=" * 30)
print("调试完成!")